package com.zmj.sy.mom.srv.aps.mapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.zmj.sy.mom.srv.aps.bean.bo.aps.DetailPhaseMapBo;
import com.zmj.sy.mom.srv.aps.bean.dto.wms.PartsReportingAddDto;
import com.zmj.sy.mom.srv.aps.bean.entity.order.ApsWorkDetail;
import com.zmj.sy.mom.srv.aps.bean.entity.order.Order;
import com.zmj.sy.mom.srv.aps.bean.entity.order.OrderBom;
import com.zmj.sy.mom.srv.aps.bean.vo.apsworkdetail.ApsWorkDetailListResVo;
import com.zmj.sy.mom.srv.aps.bean.vo.apsworkdetail.ApsWorkDetailPubListResVo;
import com.zmj.sy.mom.srv.aps.bean.vo.apsworkdetail.AssemPartsCountsResVo;
import com.zmj.sy.mom.srv.aps.bean.vo.apsworkdetail.TomorrowScheduleListVo;
import com.zmj.sy.mom.srv.aps.bean.vo.lantek.PushPartInfoResVo;
import com.zmj.sy.mom.srv.aps.bean.vo.plan.PlanMonthlyProcessDateResVo;
import com.zmj.sy.mom.srv.aps.bean.vo.plan.PlanProDetailDayResVo;
import com.zmj.sy.mom.srv.aps.bean.vo.report.ReportPlanGroupPaResVo;
import com.zmj.sy.mom.srv.aps.bean.vo.report.ReportPlanGroupPlResVo;
import com.zmj.sy.mom.srv.aps.bean.vo.report.ReportPlanGroupResVo;
import com.zmj.sy.mom.srv.aps.bean.vo.weekwork.*;
import com.zmj.sy.mom.srv.aps.bean.vo.workshop.*;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;

@Mapper
public interface ApsWorkDetailMapper extends SyMapper<ApsWorkDetail> {
    @Select(" select wo.work_order_code, wd.phase_name, wd.phase_code, wd.total_count, wd.finish_count, wd.work_time / 60  as 'workTime', wd.station_group_id, wd.group_code, s.my_code stationCode, wd.station_id, wd.plan_start_date_time, wd.plan_end_date_time, s.work_ability workAbility, s.productivity, wd.total_work_time/60 as totalWorkTime, wd.enter_time enterTime, wd.out_time outTime, wd.chang_time changTime,sws.name shiftName, wd.phase_seq phaseSeq, " +
            " (select sum(ff1.total_work_time)/60 from tb_shift ff1 where ff1.shift_type_id  = sws.shift_type_id) as shiftTotalWorkTime ," +
            " (select sum(ff2.valid_time)/60 from tb_shift ff2 where ff2.shift_type_id  = sws.shift_type_id ) AS shiftValidTime " +
            " from tb_aps_work_detail wd  " +
            " left join tb_aps_work_order wo on wo.id = wd.aps_work_order_id and wo.deleted = 0 " +
            " left join tb_station s on s.id = wd.station_id and s.deleted = 0 " +
            " left join tb_station_weekday_scheme sws on sws.station_id = s.id and sws.deleted = 0 " +
            " left join tb_shift_type st on sws.shift_type_id = st.id " +
            " ${ew.customSqlSegment} ")
    List<ApsWorkDetailListResVo> selectByWorkOrderId(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> wrapper);

    @Select(" select wd.* " +
            " from tb_aps_work_detail wd  " +
            " left join tb_order tbo  on wd.order_id = tbo.id and tbo.order_type<>2  and tbo.deleted=0 and tbo.order_status in (7,8)   " +
            " ${ew.customSqlSegment} ")
    ApsWorkDetail selectByOrder(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> wrapper);
    @Select(" select wd.* " +
            " from tb_aps_work_detail wd  " +
            " left join tb_order tbo  on wd.order_id = tbo.id and tbo.order_type=1  and tbo.deleted=0 and tbo.order_status in (7,8)  " +
            " ${ew.customSqlSegment} ")
    List<ApsWorkDetail>  selectPpWorkDetail(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> wrapper);

    @Select(" select wd.* " +
            " from tb_aps_work_detail wd  " +
            " left join tb_order tbo  on wd.order_id = tbo.id and tbo.order_type<>2  and tbo.deleted=0 and tbo.order_status in (7,8)  " +
            " ${ew.customSqlSegment} ")
    List< ApsWorkDetail> selectDetailByOrder(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> wrapper);

    @Select(" delete from tb_aps_work_detail where order_id = #{id} ")
    void deleteOrderId(@Param("id") Integer id);

    @Select("select awd.id, " +
            "ao.order_code orderCode, ao.construct_no constructNo,  " +
            "s.my_code stationCode,  s.name stationName, " +
            "g.my_code groupCode, g.name groupName, " +
            "awo.erp_code materNo, awd.mater_name materName, awd.plan_start_date_time planStartDateTime, awd.plan_end_date_time planEndDateTime, awd.status, awd.total_count ,awd.finish_count , awd.phase_code, awd.phase_name, " +
            "awo.work_order_code, awo.plm_id, awd.status status " +
            "from tb_aps_work_detail awd " +
            "left join tb_order o on awd.order_id = o.id " +
            "left join tb_aps_order ao on awd.aps_order_id = ao.id " +
            "left join tb_station s on awd.station_id = s.id " +
            "left join tb_group g on awd.group_id = g.id  " +
            "left join tb_aps_work_order awo on awd.aps_work_order_id = awo.id " +
            "${ew.customSqlSegment} ")
    Page<ApsWorkDetailPubListResVo> pubList(Page<Object> page, @Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> wrapper);

//    @Select("select t1.id,\n" +
//            "              t3.construct_no, -- 施工号\n" +
//            "              t2.work_order_code, -- 工单号\n" +
//            "              t1.mater_no, -- 物料编码\n" +
//            "              t1.mater_name, -- 物料名称\n" +
//            "              t1.phase_code, -- 工序编码\n" +
//            "              t1.phase_name, -- 工序名称\n" +
//            "              t2.drawing, -- 零件图\n" +
//            "              t2.standards, -- 规格\n" +
//            "              t2.texture, -- 材质\n" +
//            "              t1.total_count, -- 计划数量\n" +
//            "              t1.finish_count, -- 完工数量\n" +
//            "              t1.status, -- 工单状态\n" +
//            "              t1.group_code, -- 当前工作组\n" +
//            "              t1.plan_start_date_time, -- 计划开始时间\n" +
//            "              t1.plan_end_date_time, -- 计划完成时间\n" +
//            "              null actualStartDateTime, -- 实际开始时间\n" +
//            "              null actualEndDateTime -- 实际结束时间\n" +
//
//            "            from tb_aps_work_detail t1\n" +
//            "              inner join tb_aps_work_order t2 on t1.aps_work_order_id=t2.id\n" +
//            "              inner join tb_order t3 on t1.order_id=t3.id\n" +
//            "            ${ew.customSqlSegment} ")
   @Select(" select task.id, o.construct_no, wo.work_order_code,wd.phase_seq, wd.aps_work_order_id, wd.mater_no materCode, wd.mater_name, wd.phase_code, wd.phase_name, wo.drawing, wo.standards, wo.texture, task.total_count, task.finish_count, task.`status`, task.group_code, wd.plan_start_date_time, wd.plan_end_date_time, task.actual_start_date_time, task.actual_end_date_time, task.part_path " +
            " from tb_mes_work_detail_task task " +
            " left join tb_aps_work_detail wd on task.aps_work_detail_id = wd.id  " +
            " left join tb_aps_work_order wo on wd.aps_work_order_id = wo.id " +
            " left join tb_order o on wo.order_id = o.id " +
            " ${ew.customSqlSegment} ")
    Page<WorkshopPartToPartResVo> listPartToPart(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> query, Page<Object> page);


    @Select(" select wd.id, wd.unique_code as uniqueCode,o.construct_no,wd.phase_seq, wd.aps_work_order_id, wo.work_order_code work_order_code, wd.mater_no materCode, wd.mater_name, wd.phase_code, wd.phase_name, wo.standards, wo.texture, wd.total_count, " +
            " wd.finish_count, wo.mater_weight, wd.`status`, wd.group_code, wd.plan_start_date_time, wd.plan_end_date_time, wd.actual_start_date_time, wd.actual_end_date_time , ao.seq packageSeq ,wo.erp_code " +
            " from tb_aps_work_detail wd  " +
            " INNER  join tb_order o on wd.order_id = o.id and o.order_type<>2  and o.deleted=0 and o.order_status=8 "+
            " INNER join tb_aps_work_order wo on wd.aps_work_order_id = wo.id  " +
            " left join tb_aps_order ao on wo.aps_order_id = ao.id" +
            " ${ew.customSqlSegment}")
    Page<WorkshopPartToComponentResVo> listPartToComponent(Page<Object> page, @Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> query);


    @Select(" select task.id, o.construct_no, wo.work_order_code,wd.unique_code , wd.phase_seq, wd.aps_work_order_id,wd.mater_no as materCode, wd.mater_name, wd.phase_code, wd.phase_name, wo.drawing, wo.standards, wo.texture, task.total_count, task.finish_count, task.`status`, task.group_code, wd.plan_start_date_time, wd.plan_end_date_time, task.actual_start_date_time, task.actual_end_date_time " +
            " from tb_mes_work_detail_task task " +
            " left join tb_aps_work_detail wd on task.aps_work_detail_id = wd.id  " +
            " left join tb_aps_work_order wo on wd.aps_work_order_id = wo.id " +
            " left join tb_order o on wo.order_id = o.id " +
            " ${ew.customSqlSegment} ")
    Page<WorkshopComponentToComponentResVo> listComponentToComponent(Page<Object> page,@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> query);


    @Select("select min(phase_seq) from tb_aps_work_detail ${ew.customSqlSegment}")
    Integer minPhaseSeq(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> query);
    @Select("select phase_code from tb_aps_work_detail ${ew.customSqlSegment}")
    String nextPhase(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> query);

    @Select(" select awo.bom_id bomId,  sum(awo.order_count) totalCount, bom.mater_name, bom.mater_weight, bom.standards " +
            " from tb_aps_work_detail awd  " +
            " left join tb_aps_work_order awo on awo.id = awd.aps_work_order_id " +
            " left join tb_order_bom bom on awo.bom_id = bom.id " +
            " where (awd.phase_code = 'JG' or awd.phase_code = 'HG') and awd.order_id = #{id} " +
            " group by awo.bom_id ")
    List<OrderBom> findSplitBom(@Param("id")Integer id);

    @Select("delete from tb_aps_work_detail where phase_id = #{id} ")
    void deleteByPhaseId(@Param("id")Integer id);


    @Select("SELECT awd.mater_no AS assemblyNumber,\n" +
            "            MIN( IFNULL( mp.part_count, 0 )/ zob.part_count)  AS totalCount \n" +
            "            FROM tb_aps_work_detail  awd \n" +
            "            left  join  tb_order_bom ob on awd.mater_no=ob.plm_id  and ob.order_id=awd.order_id  \n" +
            "            LEFT JOIN tb_order_bom zob ON ob.bom_code = zob.parent_bom_code  and zob.deleted=0 and zob.order_id=awd.order_id  \n" +
            "            INNER  JOIN tb_order tor on zob.order_id=tor.id and tor.order_type<>2  and tor.deleted=0 and tor.order_status=8 " +
            "            LEFT JOIN tb_mes_part mp ON zob.plm_id = mp.plm_id AND mp.phase_code = 'KJ' \n" +
            "            where awd.status<4 and zob.deleted = 0 and awd.order_type<>2 and group_id =#{groupId}\n" +
            "            GROUP BY awd.mater_no\n" +
            "            HAVING totalCount>0" )
    List<WaitWorkAsseVo> getWaitAsseWorks(@Param("groupId") Integer  groupId);

    @Select("SELECT 1 as complateCount ,o.construct_no as cuconstructNo,2 as multipleType,awd.phase_code nxphase  ,awo.pack_center as opCode,awo.work_order_code as orderNo,2 as palletExist,awd.unique_code as  palletNumber,awd.mater_no as plmid,2 as requestState,awd.unique_code as uniqueSn,awo.erp_code,awd.mater_name \n" +
            " from   tb_aps_work_detail awd LEFT JOIN tb_order o on awd.order_id=o.id   \n" +
            " left join tb_aps_work_order awo on awd.aps_work_order_id =awo.id\n" +
            "where  awd.id=#{id}")
    PartsReportingAddDto getAssePartInfo(@Param("id") Integer id);

    @Select("SELECT o.order_code AS orderCode, ao.seq AS packageNum, awd.total_count AS partCount, awd.mater_no AS partCode, awd.mater_name AS partName, tsd.texture AS texture, tsd.thick_ness AS thickness, ob.standards AS standards, tsp.`status` AS pushStatus, tsp.plan_amount AS pushCount, ao.delivery_date AS deliveryTime FROM tb_aps_work_detail awd LEFT JOIN tb_order o ON awd.order_id=o.id LEFT JOIN tb_aps_order ao ON awd.aps_order_id=ao.id LEFT JOIN tb_aps_work_order awo ON awd.aps_work_order_id=awo.id LEFT JOIN tb_order_bom ob ON awo.bom_id = ob.id LEFT JOIN tb_third_sinocam_pushplan tsp ON awd.id=tsp.nest_plan_id LEFT JOIN tb_third_sinocam_pushdrawing tsd ON tsp.uni_mater_code = tsd.uni_mater_code ${ew.customSqlSegment}")
    Page<PushPartInfoResVo> pushPartInfoDetailNext(Page<Object> page, @Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> qw);

    @Select("SELECT SUM(d.total_count) FROM tb_aps_work_detail d WHERE d.phase_code IN ('HG','JG') AND d.order_id=#{orderId}")
    BigDecimal getTotal(@Param("orderId") Integer orderId);
    @Select("SELECT wdt.plmid assemblyNumber ,wdt.mater_name partName, awd.unique_code uniqueCode,o.construct_no orderNo from tb_mes_work_detail_task wdt \n" +
            "LEFT JOIN tb_aps_work_detail awd on wdt.aps_work_detail_id=awd.id\n" +
            "left join tb_order o on awd.order_id=o.id and o.order_type<>2  and o.deleted=0 and o.order_status=8 \n" +
            "where wdt.status<3 and wdt.group_code=#{ptGroup} ORDER BY o.id")
    List<SprayMesMaterialDto> findSprayMesMaterialDto(@Param("ptGroup")String ptGroup);

    @Select("DELETE FROM tb_aps_work_detail WHERE id = #{id}")
    void deleteMesNest(@Param("id") Integer id);

    @Select("DELETE FROM tb_aps_work_detail WHERE mater_no = #{plateCode}")
    void deleteMesNestWithCode(@Param("plateCode") String plateCode);

    @Select("WITH report AS ( " +
            "SELECT SUM( awdt.total_count ) totalCount, SUM( awdt.finish_count ) finishCount, mater_no, phase_code, order_id FROM tb_aps_work_detail awdt " +
            "GROUP BY mater_no, phase_code, order_id ) " +
            "SELECT awd.phase_id AS id, awd.order_id AS orderId, o.construct_no AS constructNo, awd.mater_no AS materCode, awd.mater_name AS materName,awd.phase_code AS phaseCode, " +
            "awd.phase_name AS phaseName, MAX( awo.mater_weight ) AS perWeight, MAX( awd.work_time ) AS perWorkTime, report.totalCount AS totalCount, report.finishCount AS finishCount " +
            "FROM tb_aps_work_detail awd LEFT JOIN tb_aps_work_order awo ON awd.aps_work_order_id = awo.id LEFT JOIN tb_order o ON awd.order_id = o.id LEFT JOIN report ON awd.mater_no = report.mater_no " +
            "AND awd.phase_code = report.phase_code AND awd.order_id = report.order_id  " +
            " ${ew.customSqlSegment} " +
            "GROUP BY awd.phase_id,awd.order_id,o.construct_no,awd.order_id,awd.mater_no,awd.mater_name,awd.phase_code,awd.phase_name,report.totalCount,report.finishCount ")
    List<WeekWorkInfoVo> getWeekWorkOne(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> query);

    @Select("SELECT o.construct_no AS constructNo,awd.phase_code AS phaseCode,awd.plm_id AS materCode,DATE(awd.plan_start_date_time) AS date, SUM( awd.total_count ) AS count " +
            "FROM tb_aps_work_detail awd " +
            "LEFT JOIN tb_aps_work_order awo ON awd.aps_work_order_id = awo.id " +
            "LEFT JOIN tb_order o ON awd.order_id = o.id " +
            " ${ew.customSqlSegment} " +
            "GROUP BY o.construct_no,awd.phase_code,awd.plm_id,DATE(awd.plan_start_date_time) ")
    List<WeekWorkDetail> getDayDetail(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> query);

    @Select("SELECT o.id as oid, awd.phase_id AS pid, DATE(DATE_ADD(awd.plan_start_date_time, INTERVAL -6 HOUR)) AS date, SUM( awd.total_count ) AS count " +
            "FROM tb_aps_work_detail awd LEFT JOIN tb_aps_work_order awo ON awd.aps_work_order_id = awo.id LEFT JOIN tb_order o ON awd.order_id = o.id " +
            " ${ew.customSqlSegment} " +
            "GROUP BY o.id, awd.phase_id, DATE(DATE_ADD(awd.plan_start_date_time, INTERVAL -6 HOUR)) ")
    List<WeekWorkDetail> getDayDetailOne(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> query);

    /*@Select("SELECT o.id as oid, awd.phase_id AS pid, DATE(DATE_ADD(awd.plan_start_date_time, INTERVAL -6 HOUR)) AS date, SUM( awd.total_count ) AS count " +
            "FROM tb_aps_work_detail awd LEFT JOIN tb_aps_work_order awo ON awd.aps_work_order_id = awo.id LEFT JOIN tb_order o ON awd.order_id = o.id " +
            " ${ew.customSqlSegment} " +
            "GROUP BY o.id, awd.phase_id, DATE(DATE_ADD(awd.plan_start_date_time, INTERVAL -6 HOUR)) ")
    List<WeekWorkDetail> getDayDetailCLOne(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> query);*/

    @Select("SELECT o.id as oid, awd.phase_id AS pid, DATE(DATE_ADD(awd.plan_start_date_time, INTERVAL -8 HOUR)) AS date, SUM( awd.total_count ) AS count " +
            "FROM tb_aps_work_detail awd LEFT JOIN tb_aps_work_order awo ON awd.aps_work_order_id = awo.id LEFT JOIN tb_order o ON awd.order_id = o.id " +
            " ${ew.customSqlSegment} " +
            "GROUP BY o.id, awd.phase_id, DATE(DATE_ADD(awd.plan_start_date_time, INTERVAL -8 HOUR)) ")
    List<WeekWorkDetail> getDayDetailTwo(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> query);

    @Select("SELECT o.id as oid, awd.phase_id AS pid, DATE(DATE_ADD(awd.plan_start_date_time, INTERVAL -24 HOUR)) AS date, SUM( awd.total_count ) AS count " +
            "FROM tb_aps_work_detail awd LEFT JOIN tb_aps_work_order awo ON awd.aps_work_order_id = awo.id LEFT JOIN tb_order o ON awd.order_id = o.id " +
            " ${ew.customSqlSegment} " +
            "GROUP BY o.id, awd.phase_id, DATE(DATE_ADD(awd.plan_start_date_time, INTERVAL -24 HOUR)) ")
    List<WeekWorkDetail> getDayDetailThree(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> query);

    /*@Select("SELECT awd.phase_id AS id, awd.order_id AS orderId, o.construct_no AS constructNo, awd.mater_no AS materCode, awd.mater_name AS materName, awd.phase_code AS phaseCode, " +
            "awd.phase_name AS phaseName, MAX( awo.mater_weight ) AS perWeight, MAX( awd.work_time ) AS perWorkTime, SUM(awd.total_count) AS totalCount, SUM(awd.finish_count) AS finishCount " +
            "FROM tb_aps_work_detail awd LEFT JOIN tb_aps_work_order awo ON awd.aps_work_order_id = awo.id LEFT JOIN tb_order o ON awd.order_id = o.id " +
            " ${ew.customSqlSegment} " +
            "GROUP BY awd.phase_id, awd.order_id, o.construct_no, awd.order_id, awd.mater_no, awd.mater_name, awd.phase_code, awd.phase_name ")
    List<WeekWorkInfoVo> getBackList(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> query);

    @Select("SELECT MIN( awd.phase_id ) AS id, o.id AS orderId, o.construct_no AS constructNo, o.mater_code AS materCode, o.mater_name AS materName, awd.phase_code AS phaseCode, " +
            "awd.phase_name AS phaseName, (SUM( awo.mater_weight ) / COUNT(DISTINCTROW awd.phase_id)) AS perWeight, (SUM( awd.work_time ) / COUNT(DISTINCTROW awd.phase_id)) AS perWorkTime, " +
            "(SUM(awd.total_count) / COUNT(DISTINCTROW awd.phase_id)) AS totalCount, (SUM(awd.finish_count) / COUNT(DISTINCTROW awd.phase_id)) AS finishCount " +
            "FROM tb_aps_work_detail awd LEFT JOIN tb_aps_work_order awo ON awd.aps_work_order_id = awo.id LEFT JOIN tb_order o ON awd.order_id = o.id " +
            " ${ew.customSqlSegment} " +
            "GROUP BY o.id, o.construct_no, awd.order_id, o.mater_code, o.mater_name, awd.phase_code, awd.phase_name ")
    List<WeekWorkInfoVo> getBackListCL(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> query);*/

 @Select("SELECT MIN( awd.phase_id ) AS id, o.id AS orderId, o.construct_no AS constructNo, o.mater_code AS materCode, o.mater_name AS materName, awd.phase_code AS phaseCode, " +
         "awd.phase_name AS phaseName, MAX(awow.perWeight) AS perWeight, (SUM( awd.work_time ) / o.order_qty ) AS perWorkTime, MAX(o.order_qty) AS totalCount, CEILING(SUM( awd.finish_count ) / COUNT(DISTINCTROW awd.phase_id)) AS finishCount " +
         "FROM tb_aps_work_detail awd LEFT JOIN tb_aps_work_order awo ON awd.aps_work_order_id = awo.id LEFT JOIN tb_order o ON awd.order_id = o.id " +
         "LEFT JOIN (SELECT SUM(awow.mater_weight) AS perWeight, awow.aps_order_id AS awoId FROM tb_aps_work_order awow WHERE awow.work_chain LIKE '%PT%' GROUP BY awow.aps_order_id) awow ON awow.awoId = awd.aps_order_id " +
         " ${ew.customSqlSegment} " +
         "GROUP BY o.id, o.construct_no, awd.order_id, o.mater_code, o.mater_name, awd.phase_code, awd.phase_name ")
 List<WeekWorkInfoVo> getBackListOne(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> query);

 @Select("SELECT MIN( awd.phase_id ) AS id, o.id AS orderId, o.construct_no AS constructNo, o.mater_code AS materCode, o.mater_name AS materName, awd.phase_code AS phaseCode, " +
         "awd.phase_name AS phaseName, MAX(awow.perWeight) AS perWeight, (SUM( awd.work_time ) / o.order_qty ) AS perWorkTime, MAX(o.order_qty) AS totalCount, CEILING(SUM( awd.finish_count ) / COUNT(DISTINCTROW awd.phase_id)) AS finishCount " +
         "FROM tb_aps_work_detail awd LEFT JOIN tb_aps_work_order awo ON awd.aps_work_order_id = awo.id LEFT JOIN tb_order o ON awd.order_id = o.id " +
         "LEFT JOIN (SELECT SUM(awow.mater_weight) AS perWeight, awow.aps_order_id AS awoId FROM tb_aps_work_order awow WHERE awow.work_chain LIKE '%PZ%' OR awow.work_chain LIKE '%WH%' GROUP BY awow.aps_order_id) awow ON awow.awoId = awd.aps_order_id " +
         " ${ew.customSqlSegment} " +
         "GROUP BY o.id, o.construct_no, awd.order_id, o.mater_code, o.mater_name, awd.phase_code, awd.phase_name ")
 List<WeekWorkInfoVo> getBackListTwo(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> query);

 @Select("SELECT MIN( awd.phase_id ) AS id, o.id AS orderId, o.construct_no AS constructNo, o.mater_code AS materCode, o.mater_name AS materName, awd.phase_code AS phaseCode, " +
         "awd.phase_name AS phaseName, MAX(awow.perWeight) AS perWeight, (SUM( awd.work_time ) / o.order_qty ) AS perWorkTime, MAX(o.order_qty) AS totalCount, CEILING(SUM( awd.finish_count ) / COUNT(DISTINCTROW awd.phase_id)) AS finishCount " +
         "FROM tb_aps_work_detail awd LEFT JOIN tb_aps_work_order awo ON awd.aps_work_order_id = awo.id LEFT JOIN tb_order o ON awd.order_id = o.id " +
         "LEFT JOIN (SELECT SUM(awow.mater_weight) AS perWeight, awow.aps_order_id AS awoId FROM tb_aps_work_order awow WHERE awow.work_chain LIKE '%PZ%' GROUP BY awow.aps_order_id) awow ON awow.awoId = awd.aps_order_id " +
         " ${ew.customSqlSegment} " +
         "GROUP BY o.id, o.construct_no, awd.order_id, o.mater_code, o.mater_name, awd.phase_code, awd.phase_name ")
 List<WeekWorkInfoVo> getBackListThree(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> query);

 @Select("SELECT awd.mater_no AS assemblyNumber, zob.plm_id AS plmId, tor.construct_no AS constructNo, MIN( IFNULL( mp.part_count, 0 )/ zob.part_count ) AS totalCount " +
         "FROM tb_aps_work_detail awd LEFT JOIN tb_order_bom ob ON awd.mater_no = ob.plm_id LEFT JOIN tb_order_bom zob ON ob.bom_code = zob.parent_bom_code " +
            "INNER JOIN tb_order tor ON zob.order_id = tor.id AND tor.order_type <> 2 AND tor.deleted = 0 AND tor.order_status = 8 LEFT JOIN tb_mes_part mp ON zob.plm_id = mp.plm_id " +
            "AND mp.phase_code = 'KJ' AND mp.construct_no = tor.construct_no " +
            " ${ew.customSqlSegment} " +
            "GROUP BY awd.mater_no, zob.plm_id, tor.construct_no ")
    List<AssemPartsCountsResVo> getPartsCount(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> query);

    @Select("SELECT awo.id AS id, awo.work_order_code AS workOrderCode, awd.mater_no AS materCode, awd.mater_name AS materName, ao.seq AS packageCode, MAX( awo.order_count ) AS totalPlan, MAX( awo.finish_count ) AS finishPlan, " +
            "( MAX( awo.order_count ) - MAX( awo.finish_count ) ) AS leftPlan, MIN( awo.plan_start_date_time ) AS planStartTime, MAX( awo.plan_end_date_time ) AS planEndTime " +
            "FROM tb_aps_work_detail awd LEFT JOIN tb_aps_work_order awo ON awd.aps_work_order_id = awo.id LEFT JOIN tb_order o ON awd.order_id = o.id LEFT JOIN tb_aps_order ao ON awd.aps_order_id = ao.id " +
            " ${ew.customSqlSegment} " +
            "GROUP BY awo.id, awo.work_order_code, awd.mater_no, awd.mater_name, ao.seq ")
    List<PlanDetailResVo> getPlanDetail(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> query);

    @Select("SELECT awd.aps_work_order_id AS id, awd.phase_code AS phaseCode, awd.phase_name AS phaseName, SUM(awd.total_count) AS totalPlan, SUM(awd.finish_count) AS finishPlan, " +
            "( SUM(awd.total_count) - SUM(awd.finish_count) ) AS leftPlan, MIN(awd.plan_start_date_time) AS planStartTime, MAX(awd.plan_end_date_time) AS planEndTime " +
            "FROM tb_aps_work_detail awd " +
            " ${ew.customSqlSegment} " +
            "GROUP BY awd.aps_work_order_id, awd.phase_code, awd.phase_name, awd.phase_seq " +
            "ORDER BY awd.phase_seq DESC ")
    List<PhaseDetailResVo> getPhaseDetail(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> query);

    @Select("SELECT SUM(awd.total_count) AS count FROM tb_aps_work_detail awd " +
            " ${ew.customSqlSegment} " +
            "GROUP BY awd.aps_work_order_id ")
    PlanDetailDay getDayPhaseDetail(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> query);

    @Select(" SELECT distinct awd.order_id " +
            " FROM tb_aps_work_detail awd " +
            " left join tb_aps_work_detail awd2 on awd.aps_work_order_pid = awd2.aps_work_order_id " +
            " left join tb_order o on awd.order_id = o.id " +
            " ${ew.customSqlSegment} ")
    List<Integer> orderIdList(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> query);

    @Select(" SELECT distinct awd.order_id " +
            " FROM tb_aps_work_detail awd " +
            " LEFT JOIN tb_aps_work_order awo on awd.aps_work_order_id = awo.id " +
            " left join tb_order o on awd.order_id = o.id " +
            " ${ew.customSqlSegment} ")
    List<Integer> orderIdList2(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> query);

    @Select("select awo.bom_id bomId, " +
            "awd.order_id orderId," +
            " awo.plm_id plmId, " +
            "sum(awd.total_count) totalCount, " +
            "sum(awd.finish_count) finishCount, " +
            "max(awo.part_count) partCount, " +
            "max(awo2.total_count) parentPartCount, " +
            "DATE_FORMAT(DATE_SUB(awd.plan_start_date_time, INTERVAL 7 HOUR), '%Y-%m-%d') as dateStr " +
            "from tb_aps_work_detail awd  " +
            "LEFT JOIN tb_aps_work_order awo on awd.aps_work_order_id = awo.id  " +
            "LEFT JOIN tb_aps_work_order awo2 on awd.aps_work_order_pid = awo2.id  " +
            "${ew.customSqlSegment}" )
    List<PlanMonthlyProcessDateResVo> cutList(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> cutWrapper);


 @Select(" select awo.bom_id bomId, " +
         "awd.order_id orderId, " +
         "awo.plm_id plmId, " +
         "sum(awd.total_count) totalCount, " +
         "sum(awd.finish_count) finishCount, " +
         "max(awo.part_count) partCount, " +
         "max(awo2.total_count) parentPartCount, " +
         "DATE_FORMAT(DATE_SUB(awd.plan_start_date_time, INTERVAL 7 HOUR), '%Y-%m-%d') as dateStr " +
         "from tb_aps_work_detail awd  " +
         "LEFT JOIN tb_aps_work_order awo on awd.aps_work_order_id = awo.id  " +
         "LEFT JOIN tb_aps_work_order awo2 on awd.aps_work_order_pid = awo2.id " +
         "left join tb_order o on awd.order_id = o.id  " +
         "${ew.customSqlSegment}" )
 List<PlanMonthlyProcessDateResVo> cebanCutList(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> cutWrapper);

 @Select(" select awo.bom_id bomId, " +
         "awd.order_id orderId, " +
         "awo.plm_id plmId, " +
         "sum(awd.total_count) totalCount, " +
         "sum(awd.finish_count) finishCount, " +
         "max(awo.part_count) partCount, " +
         "max(awo2.total_count) parentPartCount, " +
         "DATE_FORMAT(awd.plan_start_date_time, '%Y-%m-%d') as dateStr" +
         " from tb_aps_work_detail awd  " +
         " LEFT JOIN tb_aps_work_order awo on awd.aps_work_order_id = awo.id  " +
         " LEFT JOIN tb_aps_work_order awo2 on awd.aps_work_order_pid = awo2.id  " +
         " ${ew.customSqlSegment} " )
 List<PlanMonthlyProcessDateResVo> kjList(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> cutWrapper);


 @Select(" select awo.bom_id bomId, awd.order_id orderId, awo.plm_id plmId, sum(awd.total_count) totalCount, sum(awd.finish_count) finishCount, max(awo.part_count) partCount, max(awo.total_count) parentPartCount, DATE_FORMAT(awd.plan_start_date_time, '%Y-%m-%d') as dateStr" +
            " FROM tb_aps_work_detail awd  " +
            " LEFT JOIN tb_aps_work_order awo on awd.aps_work_order_id = awo.id " +
            " ${ew.customSqlSegment} " )
    List<PlanMonthlyProcessDateResVo> whList(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> weldingWrapper);


    @Select(" select awd.phase_code phaseCode, awd.order_id orderId, sum(awd.total_count) planCount, sum(awd.finish_count) finishCount, max(awo.part_count) partCount, max(awo.total_count) parentPartCount, DATE_FORMAT(DATE_SUB(awd.plan_start_date_time, INTERVAL 7 HOUR), '%Y-%m-%d') as dateStr, DATE_FORMAT(DATE_SUB(awd.actual_end_date_time, INTERVAL 7 HOUR), '%Y-%m-%d') as finishDateStr " +
            " FROM tb_aps_work_detail awd  " +
            " LEFT JOIN tb_aps_work_order awo on awd.aps_work_order_id = awo.id " +
            " ${ew.customSqlSegment} " )
    List<PlanProDetailDayResVo> weldingArea1(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> query);


    @Select(" select awd.phase_code phaseCode, awd.order_id orderId, awo.bom_id, awo.bom_pid, sum(awd.total_count) planCount, sum(awd.finish_count) finishCount, max(awo.part_count) partCount, max(awo.total_count) parentPartCount, DATE_FORMAT(DATE_SUB(awd.plan_start_date_time, INTERVAL 7 HOUR), '%Y-%m-%d') as dateStr, DATE_FORMAT(DATE_SUB(awd.actual_end_date_time, INTERVAL 7 HOUR), '%Y-%m-%d') as finishDateStr " +
            " FROM tb_aps_work_detail awd  " +
            " LEFT JOIN tb_aps_work_order awo on awd.aps_work_order_id = awo.id " +
            " ${ew.customSqlSegment} " )
    List<PlanProDetailDayResVo> weldingArea(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> query);



    @Select(" select awd.phase_code phaseCode, awd.order_id orderId, awd.bom_id, awd.bom_pid, awd.order_count planCount, awd.part_count partCount, awd.total_count parentPartCount, DATE_FORMAT(DATE_SUB(awd.plan_start_date_time, INTERVAL 7 HOUR), '%Y-%m-%d') as dateStr " +
            " FROM tb_ado_work_detail awd  " +
            " ${ew.customSqlSegment} " )
    List<PlanProDetailDayResVo> weldingArea33(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> query);

    @Select(" select awo.bom_id bomId, awd.order_id orderId, awo.plm_id plmId, sum(awd.total_count) planCount, max(awo.part_count) partCount, max(awo.total_count) parentPartCount, DATE_FORMAT(DATE_SUB(awd.plan_start_date_time, INTERVAL 7 HOUR), '%Y-%m-%d') as dateStr " +
            " FROM tb_aps_work_detail awd  " +
            " LEFT JOIN tb_aps_work_order awo on awd.aps_work_order_id = awo.id " +
            " ${ew.customSqlSegment} " )
    List<PlanProDetailDayResVo> cuttingArea(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> query);


    @Select(" SELECT awd.order_id, awo.bom_id, awo.plm_id plmId,  DATE_FORMAT(DATE_SUB(wdt.create_date_time, INTERVAL 7 HOUR), '%Y-%m-%d') AS finishDateStr, 0 planCount, SUM(wdt.success_count) AS finishCount " +
            " FROM tb_mes_work_record wdt " +
            " LEFT JOIN tb_aps_work_detail awd  ON wdt.aps_work_detail_id = awd.id " +
            " LEFT JOIN tb_aps_work_order awo  ON awd.aps_work_order_id = awo.id " +
            " ${ew.customSqlSegment} ")
    List<PlanProDetailDayResVo> cuttingAreaFinish(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> assemblyWrapper, @Param("orderId") String orderId);

    @Select("SELECT d.phase_code AS phaseCode, p.name AS phaseName, d.group_code AS groupCode, g.name AS groupName, SUM(d.total_count) AS planCount, SUM(d.finish_count) AS finishCount, " +
            "SUM( d.total_count * d.work_time ) AS planWorkTime, SUM( d.finish_count * d.work_time ) AS finishWorkTime " +
            "FROM tb_aps_work_detail d LEFT JOIN tb_order o ON d.order_id = o.id LEFT JOIN tb_phase p ON d.phase_code = p.my_code LEFT JOIN tb_group g ON d.group_code = g.my_code " +
            " ${ew.customSqlSegment} " +
            "GROUP BY d.phase_code, p.name, d.group_code, g.name ")
    List<ReportPlanGroupResVo> getPlanFinishPlate(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> query);

    @Select("SELECT awd.phase_code AS phase_code, awd.group_code AS groupCode, mpp.nest_id AS nestId, awd.mater_no AS plateCode, mpp.picking_list_no AS pickListNo, wdt.station_code AS stationCode, " +
            "awd.`status` AS STATUS, awd.plan_start_date_time AS planStartTime, awd.actual_start_date_time AS actualStartTime, awd.plan_end_date_time AS dueDate, awd.actual_end_date_time AS workTime " +
            "FROM tb_aps_work_detail awd LEFT JOIN tb_mes_plate_pick mpp ON awd.mater_no = mpp.material_id LEFT JOIN tb_mes_work_detail_task wdt ON awd.id = wdt.aps_work_detail_id " +
            " ${ew.customSqlSegment} ")
    List<ReportPlanGroupPlResVo> planFinishDetailPl(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> query);

    @Select("SELECT awd.phase_code AS phaseCode, awd.group_code AS groupCode, o.construct_no AS constructCode, ao.seq AS packageCode, awd.mater_name AS materName, awd.plm_id AS plmId, awo.plm_id AS parentPlmId, " +
            "o.order_qty AS orderCount, SUM( awd.total_count ) AS planCount, SUM( awd.finish_count ) AS finishCount, MIN( awd.plan_start_date_time ) AS planStartTime, MIN( awd.actual_start_date_time ) AS actualStartTime, " +
            "MAX( awd.plan_end_date_time ) AS dueDate, MAX( awd.actual_end_date_time ) AS workTime " +
            "FROM tb_aps_work_detail awd LEFT JOIN tb_order o ON awd.order_id = o.id LEFT JOIN tb_aps_order ao ON awd.aps_order_id = ao.id LEFT JOIN tb_aps_work_order awo ON awd.aps_work_order_pid = awo.id " +
            " ${ew.customSqlSegment} ")
    List<ReportPlanGroupPaResVo> planFinishDetailPa(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> query);

    @Select("select awd.order_id orderId," +
            " awo.bom_id bomId," +
            " awd.phase_code," +
            " awo.plm_id plmId," +
            " DATE_FORMAT(DATE_SUB(awd.plan_start_date_time, INTERVAL 7 HOUR), '%Y-%m-%d') as dateStr," +
            " sum(awd.total_count) planCount," +
            " max(awo.part_count) partCount," +
            " max(awo.total_count) parentPartCount " +
            " FROM tb_aps_work_detail awd  " +
            " LEFT JOIN tb_aps_work_order awo on awd.aps_work_order_id = awo.id " +
            " ${ew.customSqlSegment} " )
    List<PlanProDetailDayResVo> countWorkDetail(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> query);


    @Select(" select awd.phase_code phaseCode, awd.order_id orderId, sum(awd.order_count) planCount, max(awd.part_count) partCount, max(awd.total_count) parentPartCount, DATE_FORMAT(DATE_SUB(awd.plan_start_date_time, INTERVAL 7 HOUR), '%Y-%m-%d') as dateStr " +
            " FROM tb_ado_work_detail awd  " +
            " ${ew.customSqlSegment} " )
    List<PlanProDetailDayResVo> weldingArea12(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> query);


    @Select(" select awd.phase_code phaseCode, awd.order_id orderId, awd.bom_id, awd.bom_pid, sum(awd.order_count) planCount,  max(awd.part_count) partCount, max(awd.total_count) parentPartCount, DATE_FORMAT(DATE_SUB(awd.plan_start_date_time, INTERVAL 7 HOUR), '%Y-%m-%d') as dateStr " +
            " FROM tb_ado_work_detail awd  " +
            " ${ew.customSqlSegment} " )
    List<PlanProDetailDayResVo> weldingArea22(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> query);

    @Select("SELECT\n" +
            "            awd.order_id as orderId,\n" +
            "            o.construct_no AS construNum,\n" +
            "            awo.work_order_code AS nxolsection,\n" +
            "            awd.unique_code AS nxolphaseid,\n" +
            "            awd.plm_id AS partNo,\n" +
            "            awd.mater_name AS partName,\n" +
            "            ob.standards AS dimension,\n" +
            "            awd.total_count AS planJobCount,\n" +
            "            awd.finish_count AS finishCount,\n" +
            "            awd.plan_start_date_time AS planStartDate,\n" +
            "            awd.actual_end_date_time as endDate,\n" +
            "            awd.plan_end_date_time AS dueDate,\n" +
            "            awd.phase_code AS nextWorkPlace,\n" +
            "            op.png_url AS palletingDrawingUrl,\n" +
            "            s.my_code AS workPlaceName\n" +
            "        FROM\n" +
            "            tb_aps_work_detail awd\n" +
            "                LEFT JOIN tb_order o ON awd.order_id = o.id\n" +
            "                LEFT JOIN tb_aps_work_order awo ON awd.aps_work_order_id = awo.id\n" +
            "                LEFT JOIN tb_order_bom ob ON awo.bom_id = ob.id\n" +
            "                LEFT JOIN tb_station s ON awd.station_id=s.id\n" +
            "                LEFT JOIN tb_order_part op ON awd.plm_id = op.plm_id\n" +
            "        WHERE awd.group_code = #{workPlaceName}\n" +
            "--             AND DATE_FORMAT(awd.plan_start_date_time,'y%m%d') = DATE_FORMAT(CURDATE() + INTERVAL 1 DAY,'y%m%d')\n" +
            "             and awd.plan_start_date_time >= #{startDate}\n" +
            "             and awd.plan_start_date_time < #{endDate}\n" +
            "             and awd.order_type in (1, 3, 4)\n" +
            "             and awd.order_id not in (select distinct order_id from tb_ado_order where deleted = 0)" +
            " and o.suffix not in ('C') ")
    List<TomorrowScheduleListVo> getTomorrowSchedule(@Param("workPlaceName") String workPlaceName, @Param("startDate") String startDate, @Param("endDate") String endDate);

    @Select(" select awd.phase_id phaseId, awd.id detailId " +
            " from tb_aps_work_detail awd  " +
            " ${ew.customSqlSegment} ")
    List<DetailPhaseMapBo> findPhaseId(@Param(Constants.WRAPPER) QueryWrapper<ApsWorkDetail> wrapper);

    @Select(" select o.* from  tb_order o " +
            " ${ew.customSqlSegment} ")
    Page<Order> testTable(@Param(Constants.WRAPPER) QueryWrapper<?> query, Page<?> page);
}
